﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;
using log4net;
using QualityRpt.Utils;

namespace QualityRpt.Dao.Db2
{
    class DeviceContractDetailDao:IDeviceContractDetailDao
    {
        ILog logger = LogManager.GetLogger("DeviceContractDetailDao");
        const string cachePrefix = "DCDL-";
        public DataRow Save(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.DEVICE_CONTRACT_DETAIL(CONTRACT_NO,CONTRACT_NAME,SIGNED_DATE,DEVICE_ID,SUPPLIER_ID,SUPPLIER_NAME,SPEC_ID,SPEC_NAME"+
                    ",START_NO,END_NO,DEVICE_COUNT,SOFT_VERSION,HARD_VERSION,KEY_VERSION,IS_CONTACT,IS_ESAM,IS_NEWCARD,IS_CRC,IS_SOLAR_ENEYGY,INTEGRATION_LEVEL,IS_TAMPER,CARD_RAM,CARD_TYPE,POINT_DATE,REMARK,IS_TEST,CREATED_TIME)"
                    + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    ODBCHelper.CreateInputParameter("CONTRACT_NO", OdbcType.VarChar,20, row["CONTRACT_NO"].ToString()),
                    ODBCHelper.CreateInputParameter("CONTRACT_NAME", OdbcType.VarChar,200, row["CONTRACT_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("SIGNED_DATE", OdbcType.Date, row["SIGNED_DATE"]),
                    ODBCHelper.CreateInputParameter("DEVICE_ID", OdbcType.Int, row["DEVICE_ID"]),
                    ODBCHelper.CreateInputParameter("SUPPLIER_ID", OdbcType.Int, row["SUPPLIER_ID"]),
                    ODBCHelper.CreateInputParameter("SUPPLIER_NAME", OdbcType.VarChar, 200, row["SUPPLIER_NAME"]),
                    ODBCHelper.CreateInputParameter("SPEC_ID", OdbcType.Int, row["SPEC_ID"]),
                    ODBCHelper.CreateInputParameter("SPEC_NAME", OdbcType.VarChar,20, row["SPEC_NAME"]),
                    ODBCHelper.CreateInputParameter("START_NO", OdbcType.VarChar, 16, row["START_NO"]),
                    ODBCHelper.CreateInputParameter("END_NO", OdbcType.VarChar, 16, row["END_NO"]),
                    ODBCHelper.CreateInputParameter("DEVICE_COUNT", OdbcType.Int, row["DEVICE_COUNT"]),
                    ODBCHelper.CreateInputParameter("SOFT_VERSION", OdbcType.VarChar,20, row["SOFT_VERSION"]),
                    ODBCHelper.CreateInputParameter("HARD_VERSION", OdbcType.VarChar, 20, row["HARD_VERSION"]),
                    ODBCHelper.CreateInputParameter("KEY_VERSION", OdbcType.VarChar, 20, row["KEY_VERSION"]),
                    ODBCHelper.CreateInputParameter("IS_CONTACT", OdbcType.SmallInt, row["IS_CONTACT"]),
                    ODBCHelper.CreateInputParameter("IS_ESAM", OdbcType.SmallInt, row["IS_ESAM"]),
                    ODBCHelper.CreateInputParameter("IS_NEWCARD", OdbcType.SmallInt, row["IS_NEWCARD"]),
                    ODBCHelper.CreateInputParameter("IS_CRC", OdbcType.SmallInt, row["IS_CRC"]),
                    ODBCHelper.CreateInputParameter("IS_SOLAR_ENEYGY", OdbcType.SmallInt, row["IS_SOLAR_ENEYGY"]),
                    ODBCHelper.CreateInputParameter("INTEGRATION_LEVEL", OdbcType.VarChar, 20, row["INTEGRATION_LEVEL"]),
                    ODBCHelper.CreateInputParameter("IS_TAMPER", OdbcType.SmallInt, 1, row["IS_TAMPER"]),
                    ODBCHelper.CreateInputParameter("CARD_RAM", OdbcType.Int, row["CARD_RAM"]),
                    ODBCHelper.CreateInputParameter("CARD_TYPE", OdbcType.Int, row["CARD_TYPE"]),
                    ODBCHelper.CreateInputParameter("POINT_DATE", OdbcType.Date, row["POINT_DATE"]),
                    ODBCHelper.CreateInputParameter("REMARK", OdbcType.VarChar, 20, row["REMARK"]),
                    ODBCHelper.CreateInputParameter("IS_TEST ", OdbcType.SmallInt, 1, row["IS_TEST"]),
                    ODBCHelper.CreateInputParameter("CREATED_TIME", OdbcType.DateTime, row["CREATED_TIME"]));
                rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rt != null)
            {
                row["ID"] = Convert.ToInt32(rt);
                return row;
            }
            return null;
        }

        public DataRow Update(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "UPDATE RPT.DEVICE_CONTRACT_DETAIL SET CONTRACT_NO=?,CONTRACT_NAME=?,SIGNED_DATE=?,DEVICE_ID=?,SUPPLIER_ID=?"+
                    ",SUPPLIER_NAME=?,SPEC_ID=?,SPEC_NAME=?,START_NO=?,END_NO=?,DEVICE_COUNT=?,SOFT_VERSION=?,HARD_VERSION=?,KEY_VERSION=?,IS_CONTACT=?,IS_ESAM=?,IS_NEWCARD=?"+
                    ",IS_CRC=?,IS_SOLAR_ENEYGY=?,INTEGRATION_LEVEL=?,IS_TAMPER=?,CARD_RAM=?,CARD_TYPE=?,POINT_DATE=?,REMARK=?,IS_TEST=?,CREATED_TIME=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("CONTRACT_NO", OdbcType.VarChar, 20, row["CONTRACT_NO"].ToString()),
                    ODBCHelper.CreateInputParameter("CONTRACT_NAME", OdbcType.VarChar, 200, row["CONTRACT_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("SIGNED_DATE", OdbcType.Date, row["SIGNED_DATE"]),
                    ODBCHelper.CreateInputParameter("DEVICE_ID", OdbcType.Int, row["DEVICE_ID"]),
                    ODBCHelper.CreateInputParameter("SUPPLIER_ID", OdbcType.Int, row["SUPPLIER_ID"]),
                    ODBCHelper.CreateInputParameter("SUPPLIER_NAME", OdbcType.VarChar, 200, row["SUPPLIER_NAME"]),
                    ODBCHelper.CreateInputParameter("SPEC_ID", OdbcType.Int, row["SPEC_ID"]),
                    ODBCHelper.CreateInputParameter("SPEC_NAME", OdbcType.VarChar, 20, row["SPEC_NAME"]),
                    ODBCHelper.CreateInputParameter("START_NO", OdbcType.VarChar, 16, row["START_NO"]),
                    ODBCHelper.CreateInputParameter("END_NO", OdbcType.VarChar, 16, row["END_NO"]),
                    ODBCHelper.CreateInputParameter("DEVICE_COUNT", OdbcType.Int, row["DEVICE_COUNT"]),
                    ODBCHelper.CreateInputParameter("SOFT_VERSION", OdbcType.VarChar, 20, row["SOFT_VERSION"]),
                    ODBCHelper.CreateInputParameter("HARD_VERSION", OdbcType.VarChar, 20, row["HARD_VERSION"]),
                    ODBCHelper.CreateInputParameter("KEY_VERSION", OdbcType.VarChar, 20, row["KEY_VERSION"]),
                    ODBCHelper.CreateInputParameter("IS_CONTACT", OdbcType.SmallInt, row["IS_CONTACT"]),
                    ODBCHelper.CreateInputParameter("IS_ESAM", OdbcType.SmallInt,  row["IS_ESAM"]),
                    ODBCHelper.CreateInputParameter("IS_NEWCARD", OdbcType.SmallInt, row["IS_NEWCARD"]),
                    ODBCHelper.CreateInputParameter("IS_CRC", OdbcType.SmallInt, row["IS_CRC"]),
                    ODBCHelper.CreateInputParameter("IS_SOLAR_ENEYGY", OdbcType.SmallInt, row["IS_SOLAR_ENEYGY"]),
                    ODBCHelper.CreateInputParameter("INTEGRATION_LEVEL", OdbcType.VarChar, 20, row["INTEGRATION_LEVEL"]),
                    ODBCHelper.CreateInputParameter("IS_TAMPER", OdbcType.SmallInt, row["IS_TAMPER"]),
                    ODBCHelper.CreateInputParameter("CARD_RAM", OdbcType.Int, row["CARD_RAM"]),
                    ODBCHelper.CreateInputParameter("CARD_TYPE", OdbcType.Int, row["CARD_TYPE"]),
                    ODBCHelper.CreateInputParameter("POINT_DATE", OdbcType.Date, row["POINT_DATE"]),
                    ODBCHelper.CreateInputParameter("REMARK", OdbcType.VarChar, 20, row["REMARK"]),
                    ODBCHelper.CreateInputParameter("IS_TEST ", OdbcType.SmallInt, row["IS_TEST"]),
                    ODBCHelper.CreateInputParameter("CREATED_TIME", OdbcType.DateTime, row["CREATED_TIME"]),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, row["ID"]));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            return row;
        }

        public DataRow Get(long id)
        {
            DataTable table = GetList("ID="+id);
            if (table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }

        public void Delete(long id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.DEVICE_CONTRACT_DETAIL WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, id));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
        }

        public DataTable GetList(string where)
        {
            return GetList(where, "DC.ID ASC");
        }

        public DataTable GetList(string where, string sort)
        {
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT DC.* FROM RPT.DEVICE_CONTRACT_DETAIL AS DC");
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbText.Append(" ORDER BY " + sort);
            }
            //logger.Info(sbText.ToString());
            DataTable rtTable = null;
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }

        public DataTable GetPageList(PageUtils pu, string where, string sort)
        {
            StringBuilder sbListSql = new StringBuilder();
            StringBuilder sbCountSql =new StringBuilder();
            DataTable rtTable = null;
            sbListSql.Append("SELECT DC.* FROM RPT.DEVICE_CONTRACT_DETAIL AS DC");
            if (where.Trim() != string.Empty)
            {
                sbListSql.Append(" WHERE " + where);
            }
            sbCountSql.Append("WITH WT AS(" + sbListSql.ToString() + ") SELECT COUNT(*) FROM WT");
            if (sort.Trim() != string.Empty)
            {
                sbListSql.Append(" ORDER BY " + sort);
            }
            else
            {
                sbListSql.Append(" ORDER BY ID DESC");
            }
			logger.Info (sbListSql.ToString ());
            string cachekey = cachePrefix + sbListSql +"-"+ pu.Page.ToString();
            pu.ListSql = sbListSql.ToString(); pu.TotalCountSql = sbCountSql.ToString();
            logger.Info(pu.ListSql.ToString());
            if (CacheManager.Get(cachekey) == null)
            {
                rtTable = pu.GetDb2OdbcPageList();
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
                pu.DB2OdbcExecutePage();
            }
            return rtTable;
        }
        public DataTable GetBySpecIn(string specIn,int devID,PageUtils pu)
        {
            StringBuilder sbWhere = new StringBuilder();
            if (specIn!=null && specIn.Trim() != string.Empty)
            {
                sbWhere.Append(" DC.SPEC_ID IN ("+specIn+")");
            }
            if (devID > 0)
            {
                if (sbWhere.ToString().Trim() != string.Empty)
                {
                    sbWhere.Append(" AND");
                }
                sbWhere.Append(" DC.DEVICE_ID=" + devID);
            }
            if (pu == null)
            {
                return GetList(sbWhere.ToString());
            }
            else
            {
                return GetPageList(pu, sbWhere.ToString(), string.Empty);
            }
        }
        public DataTable GetListBySpIn(int devID,string spIn,PageUtils pu)
        {
            StringBuilder sbWhere = new StringBuilder();
            if (devID > 0)
            {
                sbWhere.Append(" DC.DEVICE_ID="+devID.ToString());
            }
            if (spIn!=null && spIn.Trim() != string.Empty)
            {
                if (sbWhere.ToString().Trim() != string.Empty)
                {
                    sbWhere.Append(" AND");
                }
                sbWhere.Append(" DC.SUPPLIER_ID IN ("+spIn+")");
            }
            if (pu == null)
            {
                return GetList(sbWhere.ToString());
            }
            else
            {
                return GetPageList(pu, sbWhere.ToString(), string.Empty);
            }
        }
        public DataTable GetListByCnIn(int devID,string cnIn,PageUtils pu)
        {
            StringBuilder sbWhere = new StringBuilder();
            if (devID > 0)
            {
                sbWhere.Append(" DC.DEVICE_ID=" + devID.ToString());
            }
            if (cnIn!=null && cnIn.Trim() != string.Empty)
            {
                if (sbWhere.ToString().Trim() != string.Empty)
                {
                    sbWhere.Append(" AND");
                }
                sbWhere.Append(" DC.CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnIn) + ")");
            }
            if (pu == null)
            {
                return GetList(sbWhere.ToString());
            }
            else
            {
                return GetPageList(pu, sbWhere.ToString(), string.Empty);
            }
        }
        public DataTable GetTempOBU()
        {
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT * FROM RPT.TEMP_OBU ");
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                return ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
            }
        }
        public void SaveTable(string cnno, DataTable table)
        {
            string strwhere = "CONTRACT_NO='" + cnno + "'";
            DataTable eTable = GetList(strwhere);
            if (eTable.Rows.Count > table.Rows.Count)
            {
                while (eTable.Rows.Count > table.Rows.Count)
                {
                    DataRow delRow = eTable.Rows[eTable.Rows.Count - 1];
                    Delete(Convert.ToInt64(delRow["ID"]));
                    eTable.Rows.Remove(delRow);
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow upRow = table.Rows[i];
                    upRow["ID"] = eTable.Rows[i]["ID"];
                    Update(upRow);
                }
            }
            else
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow curRow = table.Rows[i];
                    if (eTable.Rows.Count > i)
                    {
                        curRow["ID"] = eTable.Rows[i]["ID"];
                        Update(curRow);
                    }
                    else
                    {
                        Save(curRow);
                    }
                }
            }
        }
        public void DeleteByCn(string cnno)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.DEVICE_CONTRACT_DETAIL WHERE CONTRACT_NO=?",
                    ODBCHelper.CreateInputParameter("CONTRACT_NO", OdbcType.VarChar, cnno));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
        }
    }
}
